Understanding Row-Level Security (RLS) in Power BI

Course: Microsoft Power BI Training

Topic: Understanding Row-Level Security (RLS)

Course Link: Microsoft Power BI Training at JustAcademy

Demo Link: Register for a Course Demo

JustAcademy Website: Visit JustAcademy

Official Documentation: Microsoft Learn – Row-Level Security in Power BI

1. Introduction to Row-Level Security (RLS)

Row-Level Security (RLS) is a data security feature in Microsoft Power BI that restricts which rows of data individual users can view in a report or semantic model. It allows organizations to create reports that display different subsets of the same underlying data to different users.

For example, a company may maintain a single sales dataset containing transactions from Mumbai, Delhi, Pune, and Bengaluru. The Mumbai sales manager should only see Mumbai sales, while the Delhi sales manager should only see Delhi sales. RLS makes this possible without requiring separate reports and datasets for every manager.

RLS applies filters through security roles. These filters determine which rows are visible to a user when they query the semantic model. Depending on the requirement, filters can be fixed or dynamically determined by the identity of the signed-in user.

1.1 Why Is RLS Important?

• Data confidentiality: Prevents users from viewing rows they are not authorized to access.
• Centralized reporting: Allows multiple users to work with one shared semantic model.
• Reduced duplication: Avoids maintaining separate reports for every department, region, or employee.
• Controlled access: Supports role-based access to relevant business information.
• Improved governance: Helps organizations implement consistent data-access policies.
• Scalability: Dynamic RLS can support many users through a common security rule.

1.2 Real-World Applications

• Regional managers view only sales from their assigned regions.
• HR managers access employee records relevant to their authorized scope.
• Sales representatives see their own customers and sales performance.
• Department heads access data associated with their departments.
• Franchise owners see only the transactions belonging to their franchises.
• Financial controllers access data for their authorized business units.

2. Understanding How RLS Works

RLS works by applying security filters to tables in a Power BI semantic model. When a user accesses a report, Power BI evaluates the applicable role filters and restricts the rows returned by queries.

2.1 Basic RLS Workflow

1. Import or connect to the required data source in Power BI Desktop.
2. Build the data model and define relationships between tables.
3. Create security roles and write the required filter expressions.
4. Test the roles in Power BI Desktop.
5. Publish the semantic model and report to Power BI Service.
6. Assign users or supported security groups to the appropriate roles.
7. Test the security configuration in the service.
8. Share the report with authorized users using appropriate permissions.

2.2 Example of RLS Filtering

Consider the following sales table:

If a role filters the Region column to Mumbai, users assigned to that role can see the following rows:

The Delhi and Pune rows are excluded from the results available to that role. The underlying dataset does not need to be duplicated.

2.3 RLS Is Different from Visual-Level Filtering

A report slicer or visual filter changes what is displayed in a report, but users may be able to remove or change those filters. RLS is a security mechanism enforced by the semantic model and cannot be bypassed simply by removing a slicer or changing a visual.

RLS should not be confused with hiding report pages, hiding columns, or applying ordinary report filters. These techniques do not provide the same row-level data protection.

3. Types of Row-Level Security

Power BI RLS is commonly implemented using static security or dynamic security.

3.1 Static Row-Level Security

Static RLS uses a fixed filter condition. The role is configured to return rows that match a specific value, such as a region or department.

Example: A role named MumbaiSales can use the following filter on the Sales table:

[Region] = "Mumbai"

Users assigned to this role see only rows where the Region column is Mumbai.

Advantages:

• Simple to create and understand.
• Easy to test for a small number of fixed categories.
• Useful when each role has a clearly defined and stable data scope.

Limitations:

• Multiple regions or departments may require several roles.
• Role administration becomes more difficult as the number of users and categories increases.
• Changes to the organizational structure may require role maintenance.

3.2 Dynamic Row-Level Security

Dynamic RLS determines which rows a user can access based on the signed-in user's identity or a related user-mapping table. Instead of creating a separate role for every region or employee, an organization can create a common role that evaluates the current user's identity.

For example, a UserRegion table may map each employee's sign-in identifier to an authorized region. The model uses the current user's identity to retrieve the relevant mapping and filter the data.

Advantages:

• Scales to a large number of users.
• Reduces the need to create many fixed roles.
• Supports centralized access mapping.
• Can accommodate users who are authorized for multiple regions.

Limitations:

• Requires a correctly designed data model and user-mapping table.
• Incorrect identity values can cause users to see no data.
• Relationships and filter propagation must be tested carefully.

3.3 Static RLS vs Dynamic RLS

4. Understanding Security Roles in Power BI

A security role contains one or more filters that determine which data is available to users assigned to that role. Roles are created in Power BI Desktop for supported semantic models and published with the model.

4.1 Examples of Security Roles

• AdminReportsViewer: A business-defined role for a restricted reporting audience. This is an RLS role name, not a Power BI workspace administrator permission.
• MumbaiManager: A role that filters sales records to Mumbai.
• DelhiManager: A role that filters sales records to Delhi.
• FinanceDepartment: A role that restricts rows to the Finance department.
• EmployeeSelfService: A role that filters employee-related data according to the signed-in employee.

4.2 How Roles Are Applied

When a user accesses a semantic model, Power BI applies the filters associated with the user's assigned role or roles. If a user is assigned to multiple roles, the filters generally combine in a permissive way: rows allowed by any applicable role may be visible. Therefore, assigning a user to several roles can expand their access rather than make their permissions more restrictive.

Design and test role assignments carefully, especially when users belong to several security groups.

4.3 RLS Roles and Workspace Permissions

RLS is enforced for users with the appropriate consumption permissions, such as the Viewer workspace role or access through an app. RLS does not restrict users who have Admin, Member, or Contributor workspace roles in the same way. These workspace roles have elevated permissions and can access data beyond the restrictions intended for report viewers.

Important: Do not rely on RLS alone to restrict workspace administrators, members, or contributors. Give users only the workspace and semantic model permissions they actually need.

5. Creating RLS Roles in Power BI Desktop

5.1 Step 1: Open the Power BI Desktop File

Open the PBIX file containing the data model you want to secure. Ensure that the required tables and relationships are available before defining security roles.

5.2 Step 2: Open Manage Roles

1. Select the Modeling tab on the Power BI Desktop ribbon.
2. Select Manage roles or the equivalent role-management option in your version.
3. In the role-management window, select New.

5.3 Step 3: Create a Role

Enter a meaningful role name, such as MumbaiManager. Choose a naming convention that clearly describes the role's purpose.

5.4 Step 4: Select the Table to Filter

Select the table that contains the column used to restrict data. For example, if the Sales table includes a Region column, you can define a filter on that table.

5.5 Step 5: Define the Filter Expression

For a fixed region, enter the following DAX expression in the role's table filter:

[Region] = "Mumbai"

This expression returns TRUE for rows where Region is Mumbai and FALSE for other rows. Only rows allowed by the filter remain visible through the role.

5.6 Step 6: Save the Role

Validate the expression and save the role. Repeat the process for other static roles if required.

5.7 Step 7: Test the Role

1. Go to the Modeling tab.
2. Select View as.
3. Select the role you created.
4. Review the report to confirm that only the expected rows are visible.
5. Exit the role simulation when testing is complete.

6. Static RLS Example: Restricting Sales by Region

Assume the Sales table contains a Region column with values Mumbai, Delhi, Pune, and Bengaluru. Each regional manager should see only the sales records for their own region.

6.1 Create the Mumbai Role

[Region] = "Mumbai"

6.2 Create the Delhi Role

[Region] = "Delhi"

6.3 Create the Pune Role

[Region] = "Pune"

6.4 Create the Bengaluru Role

[Region] = "Bengaluru"

6.5 Expected Results

This design is straightforward for a small number of regions. For a large organization with hundreds of users or frequently changing assignments, dynamic RLS may be easier to maintain.

7. Understanding DAX Expressions for RLS

DAX (Data Analysis Expressions) is the formula language used to define many Power BI calculations and security filters. An RLS filter expression generally evaluates to TRUE or FALSE for each row in the table being filtered.

7.1 Basic Equality Filter

[Department] = "Finance"

Allows rows where the Department column contains Finance.

7.2 Filtering by Numeric Value

[SalesAmount] >= 10000

Allows rows where SalesAmount is at least 10,000. This is a demonstration of a row filter, not a complete financial access policy.

7.3 Filtering Using a Logical Condition

[Region] = "Mumbai" && [SalesAmount] > 0

Allows rows that meet both conditions: the region is Mumbai and the sales amount is greater than zero.

7.4 Filtering Using OR Logic

[Region] = "Mumbai" || [Region] = "Pune"

Allows rows for either Mumbai or Pune. Use parentheses when writing more complex conditions so the intended logic is clear.

7.5 Using IN for Multiple Values

[Region] IN {"Mumbai", "Pune"}

This is another way to allow multiple fixed region values.

7.6 Important DAX Considerations

• Use the correct table and column names.
• Ensure text values match the values in the data model.
• Check that the expression returns a valid Boolean result.
• Use the DAX editor for expressions that cannot be represented in the basic filter editor.
• Test the role against realistic data, including blank and unexpected values.

8. Understanding Dynamic RLS

Dynamic RLS applies filters according to the signed-in user's identity. It commonly uses a mapping table that connects users to the regions, departments, or records they are allowed to view.

8.1 What Is a User-Mapping Table?

A user-mapping table stores the relationship between a user's sign-in identifier and the data scope authorized for that user.

Example table named UserRegion:

This mapping table can be used to determine which regions are authorized for each user.

8.2 How Dynamic RLS Works

1. A user signs in to Power BI.
2. Power BI evaluates the identity available to the semantic model.
3. The RLS expression matches that identity to the user-mapping table.
4. The mapping table identifies the authorized data scope.
5. Relationships and filters propagate the restriction to the relevant data tables.
6. The user sees only the rows permitted by the security configuration.

9. Using USERPRINCIPALNAME() for Dynamic RLS

USERPRINCIPALNAME() is a DAX function commonly used to retrieve the current user's User Principal Name (UPN). A UPN often resembles an email address, such as [email protected], but it is not guaranteed to be identical to the user's email address.

9.1 Example of an Identity Filter

If the table being filtered contains a UserEmail column, the following expression can match it against the current user's UPN:

[UserEmail] = USERPRINCIPALNAME()

This pattern works when the filter table is designed so the matching identity restricts the appropriate rows. For more complex scenarios, a mapping table and relationships may be needed.

9.2 Example: User-to-Region Mapping

Suppose the UserRegion table has a UserEmail column and a Region column. A role can filter the mapping table using:

UserRegion[UserEmail] = USERPRINCIPALNAME()

For the mapping to restrict the Sales table, the model must have suitable relationships and filter propagation. A filter on the mapping table does not automatically restrict unrelated tables without a valid model path.

9.3 USERNAME() vs USERPRINCIPALNAME()

In Power BI Desktop, USERNAME() may return a value such as DOMAIN\username, while the Power BI service generally returns a UPN-style identity. Verify the actual value used by your deployment before building a production security rule.

10. Building a Dynamic RLS Data Model

10.1 Example Tables

Assume the model contains the following tables:

• Sales: SalesID, RegionID, SalesAmount, OrderDate.
• Region: RegionID, RegionName.
• UserRegion: UserEmail, RegionID.

10.2 Relationship Design

The Region table connects to Sales through RegionID. The UserRegion table maps each user to one or more RegionID values. The security design must ensure that the current user's mapping filters the permitted regions and, in turn, the Sales rows.

Before enabling bidirectional relationships or changing cross-filter directions, review the entire model. Bidirectional filtering can introduce ambiguous paths or unintended access and should only be enabled where the design requires it.

10.3 Model Structure Example

UserRegion[RegionID] -> Region[RegionID] -> Sales[RegionID]

This illustrates the intended filtering path conceptually. The actual relationship directions, cardinalities, and RLS behavior must be configured and validated in the model. If a user can access multiple regions, the mapping table should contain one valid mapping row for each authorized region.

10.4 Benefits of a Mapping Table

• Supports one-to-many or many-to-many user-to-scope mappings, when designed correctly.
• Separates security assignments from report visual logic.
• Makes organizational changes easier to maintain.
• Allows a single dynamic role to serve many users.

11. Implementing Dynamic RLS Step by Step

Step 1: Prepare the User Mapping

Create a table containing the actual UPN values and their authorized data scopes. Remove duplicate or invalid mappings unless duplicates are intentionally required by the model design.

Step 2: Load the Table

Import the user-mapping table into Power BI Desktop or connect to it using the appropriate supported storage mode.

Step 3: Create Relationships

Connect the mapping and dimension tables so the user's authorized scope can filter the relevant fact tables. Validate the relationship cardinality and filter direction.

Step 4: Create a Dynamic Role

Open Modeling > Manage roles and create a role such as DynamicRegionAccess.

Step 5: Add the Identity Filter

On the UserRegion table, define the following filter expression:

UserRegion[UserEmail] = USERPRINCIPALNAME()

Ensure that the table and column names correspond to the actual model.

Step 6: Test Different Users

Use the View as feature to simulate role behavior in Power BI Desktop. Test several identity values and verify that the expected rows are returned. Testing should include users with one region, multiple regions, and no matching mapping.

Step 7: Publish the Model

Publish the PBIX file to the intended workspace. The role definition is published with the semantic model.

Step 8: Assign Role Members

In Power BI Service, open the semantic model's security settings and add the intended users or supported security groups to the role.

Step 9: Verify in Power BI Service

Use the available role-testing feature and, where necessary, test with actual accounts that have Viewer-level access. Confirm that the data scope matches the security policy.

12. Assigning Users to RLS Roles in Power BI Service

Creating a role defines the rules, but users must also be assigned to the appropriate role in the Power BI service for the rules to apply as intended.

12.1 Steps to Assign Users

1. Sign in to Power BI Service.
2. Open the workspace containing the semantic model.
3. Locate the semantic model and open its security settings.
4. Select the role you want to manage.
5. Add the authorized user or supported security group.
6. Save the changes.
7. Verify the user's access through the appropriate test process.

12.2 Supported Group Considerations

Power BI supports specific types of groups for RLS role membership, such as Microsoft Entra security groups, mail-enabled groups, and distribution groups where supported. Microsoft 365 groups are not supported for RLS role membership. Confirm the current Microsoft documentation and tenant configuration before deploying group-based assignments.

12.3 Remove Users from Roles

When a user changes departments or leaves the organization, remove or update their role assignment and the corresponding mapping records. Review related access permissions as well; removing an RLS role assignment alone is not a substitute for reviewing all access paths.

13. Testing and Validating RLS

Testing is essential because an incorrect RLS configuration can either expose unauthorized data or prevent authorized users from seeing required information.

13.1 View As in Power BI Desktop

1. Open the report in Power BI Desktop.
2. Select Modeling > View as.
3. Select the security role to test.
4. For dynamic RLS, use the supported option to simulate another user identity where applicable.
5. Inspect tables, charts, totals, and slicers.
6. Verify that unauthorized rows are not returned.

13.2 Test As Role in Power BI Service

Power BI Service provides a role-testing feature for supported semantic models. Use it to validate that the published security rules work as expected. Be aware that the test feature does not reproduce every authentication scenario, including certain DirectQuery configurations with single sign-on.

13.3 Create a Test Matrix

14. RLS and Power BI Relationships

Relationships determine how filters move between tables in a semantic model. They are especially important for dynamic RLS because a filter applied to a user-mapping or dimension table must reach the relevant fact tables.

14.1 One-to-Many Relationships

A typical model may have a Region dimension related to a Sales fact table. A single region can have many sales rows. Filtering the Region dimension can then restrict the related Sales rows through the relationship.

14.2 Filter Direction

Single-direction relationships are common in star-schema models. Dynamic RLS designs sometimes require careful consideration of how security filters propagate through mapping tables and dimensions.

14.3 Bidirectional Cross-Filtering

Bidirectional relationships can allow filters to flow in both directions, but they may introduce ambiguity or affect model performance. Use them only when necessary and validate their impact on RLS behavior.

14.4 Disconnected Tables

A disconnected table has no relationship path to another table. Applying a filter to a disconnected mapping table will not automatically filter the Sales table. A valid relationship path or an appropriately designed security expression is needed.

15. RLS with Import and DirectQuery Models

15.1 Import Mode

In Import mode, data is loaded into the Power BI semantic model. RLS filters determine which rows a user can query from the model. Refreshing the model updates its imported data, while security evaluation controls which rows are returned to each user.

15.2 DirectQuery Mode

DirectQuery queries data at the source instead of importing all of it into the semantic model. Power BI can support RLS with DirectQuery, but the source connection, authentication, query performance, and relationship design must be considered.

15.3 Live Connections

When a report uses a live connection to an Analysis Services model, RLS is generally defined in the underlying Analysis Services model rather than directly in the Power BI report. The available security configuration depends on the connection type.

15.4 Comparison

16. Common RLS Errors and Troubleshooting

16.1 User Sees No Data

Possible causes:

• The user is not mapped in the user table.
• The UPN in the mapping table does not match the identity returned by Power BI.
• A relationship is missing or configured incorrectly.
• The role filter excludes all available rows.
• The user's assigned role or access permissions are incorrect.

Resolution: Check the actual identity value, verify mapping rows, inspect relationships, review the DAX expression, and test with the intended user's access context.

16.2 User Sees Too Much Data

Possible causes:

• The user has an elevated workspace role.
• The user belongs to multiple roles whose combined permissions allow additional rows.
• The RLS filter is applied to the wrong table.
• The security filter does not propagate to a related fact table.
• Another access path or semantic model is being used.

Resolution: Review workspace permissions, role membership, relationship paths, and the actual semantic model queried by the report.

16.3 Dynamic RLS Works in Desktop but Fails in the Service

Possible causes:

• The identity function returns a different format in Desktop and the service.
• The mapping table contains a different identifier than the user's UPN.
• Role membership was not configured after publishing.
• The user is accessing a different published model or report.

Resolution: Verify the identity value in the target environment, check the role assignment, and test the published model with a real Viewer-level account.

16.4 External Guest Users See No Data

External users invited through Microsoft Entra B2B can have UPN formats that differ from the email address stored in the mapping table. Verify the identity actually returned by Power BI for the guest account and ensure that the mapping table uses the matching value. Test with the real guest account rather than relying only on simulated role testing.

16.5 Test as Role Does Not Match Actual User Behavior

Role simulation may not reproduce every identity or authentication scenario. In particular, some DirectQuery models with single sign-on do not support the usual role-testing experience. Validate such cases using an actual account with the appropriate permissions.

17. Best Practices for Implementing RLS

• Use clear, consistent names for roles and security mapping tables.
• Prefer dynamic RLS when many users need different access scopes.
• Use a reliable source of user identity and authorization data.
• Match the actual UPN value rather than assuming it always equals an email address.
• Design relationships deliberately so security filters reach the correct tables.
• Test users with one scope, multiple scopes, and no mapping.
• Review role combinations because multiple roles can broaden access.
• Keep workspace permissions limited to the minimum necessary level.
• Do not treat hidden pages, hidden columns, or slicers as security controls.
• Review security assignments when employees change roles or leave the organization.
• Document access rules and test results for auditing.
• Validate the published semantic model, not only the Desktop file.
• Use least-privilege principles for data access and workspace membership.
• Re-test RLS whenever the data model, relationships, or user mappings change.

18. RLS vs Object-Level Security (OLS)

RLS and OLS are related security concepts, but they solve different problems.

RLS and OLS may be used together in an appropriate security design. Their configuration and support depend on the model and tools being used. Neither should be replaced by merely hiding report visuals.

19. Practical Project: Build a Region-Based RLS Report

Project Objective

Create a sales report where each regional manager can view only the sales data associated with their authorized region.

Required Data

• A Sales table with SalesID, RegionID, SalesAmount, and OrderDate.
• A Region table with RegionID and RegionName.
• A UserRegion table with UserEmail and RegionID.

Implementation Steps

1. Load the Sales, Region, and UserRegion tables into Power BI Desktop.
2. Build and validate the relationships between the tables.
3. Create report visuals such as total sales cards, regional charts, and sales trends.
4. Create a dynamic security role using the user-mapping table.
5. Apply a DAX filter based on USERPRINCIPALNAME().
6. Test access for users mapped to different regions.
7. Test users with multiple region assignments and users without mappings.
8. Publish the report and semantic model to Power BI Service.
9. Assign authorized users or supported groups to the role.
10. Verify that the published report respects the intended access rules.

Expected Outcome

Each authorized manager can see data for the region or regions assigned to them. The organization maintains one report and one semantic model while applying different data-access rules for different users.

20. Interview Questions and Answers

Q1. What is Row-Level Security in Power BI?

Answer: RLS restricts which rows a user can access in a semantic model based on filters defined in security roles.

Q2. What are the main types of RLS?

Answer: Static RLS uses fixed filter conditions, while dynamic RLS uses a user's identity or mapping data to determine the allowed rows.

Q3. What is the purpose of USERPRINCIPALNAME()?

Answer: It returns the current user's UPN and is commonly used to match a signed-in user to a security mapping table.

Q4. Where do you create RLS roles?

Answer: For supported semantic models, roles are typically created in Power BI Desktop using Modeling > Manage roles, then published with the model.

Q5. Where do you assign users to RLS roles?

Answer: Users or supported security groups are assigned to roles in Power BI Service through the semantic model's security settings.

Q6. Can RLS be bypassed by removing a report slicer?

Answer: No. RLS is enforced by the semantic model and is different from a visual or report filter.

Q7. Why might dynamic RLS return no data?

Answer: Common causes include a mismatched UPN, missing mapping rows, incorrect relationships, or an overly restrictive filter expression.

Q8. Does RLS restrict workspace administrators?

Answer: RLS does not apply to workspace Admin, Member, or Contributor roles in the same way it applies to Viewer-level users. Workspace permissions must be designed carefully.

Q9. Can a user belong to multiple RLS roles?

Answer: Yes, but the combined role filters can broaden the user's access. Test combined memberships carefully.

Q10. What is the difference between RLS and OLS?

Answer: RLS restricts rows, whereas OLS restricts access to specified model objects such as tables or columns, where supported.

Q11. Can RLS work with DirectQuery?

Answer: Yes, RLS is supported for applicable DirectQuery models, but source authentication, relationships, and connection limitations must be considered.

Q12. How do you validate RLS before deployment?

Answer: Test roles in Desktop, validate the published model in Power BI Service, and use actual Viewer-level accounts for scenarios that role simulation cannot fully reproduce.

21. Quick Revision Checklist

• RLS restricts data at the row level.
• Security rules are defined through roles and filter expressions.
• Static RLS uses fixed values, such as a specific region.
• Dynamic RLS uses user identity and mapping data.
• USERPRINCIPALNAME() returns the current user's UPN.
• Dynamic RLS requires correctly designed relationships and matching identity values.
• Role definitions are created in Desktop and published with the semantic model.
• Role membership is managed in Power BI Service.
• RLS does not restrict workspace Admin, Member, and Contributor roles in the same way as Viewer access.
• Multiple role assignments can broaden access.
• Test the published configuration and real user access.
• Use least-privilege permissions and document the security design.

22. Conclusion

Row-Level Security is an essential Power BI feature for protecting business data while allowing multiple users to work with a shared semantic model. Static RLS is suitable for simple, fixed access requirements, while dynamic RLS is often better for organizations with many users and changing data-access assignments.

A reliable RLS implementation depends on accurate DAX expressions, appropriate relationships, correct identity mapping, careful role assignment, and thorough testing. Organizations should also manage workspace permissions separately because elevated workspace roles are not restricted by RLS in the same way as Viewer-level access.

To continue learning, explore the Microsoft Power BI Training course, register for a course demo, or visit the JustAcademy website.